*Extract variables from HMRC FAME*

*FAME: 2000-2009

forv i=5/8 {

local j=`i'+1 

insheet using "H:\FAME\FAME Updated July 14\FAME0`i'0`j'.tab", clear

drop if identifier==.

qui keep identifier group_loans_asset group_loans_long_term group_loans_short_term ///
long_term_debt total_assets short_term_loans_overdrafts turnover

gen year=200`i'

qui bysort identifier: gen multi=_N
qui drop if multi>=2
qui drop multi

save "${hmrc_datadir}\FAME\fame200`i'", replace

}

insheet using "H:\FAME\FAME Updated July 14\FAME0910.tab", clear

drop if identifier==.

qui keep identifier group_loans_asset group_loans_long_term group_loans_short_term ///
long_term_debt total_assets short_term_loans_overdrafts turnover

gen year=2009

qui bysort identifier: gen multi=_N
qui drop if multi>=2
qui drop multi

save "${hmrc_datadir}\FAME\fame2009", replace


*FAME: 2010-2013


insheet using "H:\FAME\FAME Updated July 14\FAME_1011.tab", clear

qui drop if identifier==.

qui keep identifier group_loans_asset group_loans_long_term group_loans_short_term ///
long_term_debt total_assets short_term_loans_overdrafts turnover
 
gen year=2010

qui bysort identifier: gen multi=_N
qui drop if multi>=2
qui drop multi

save "${hmrc_datadir}\FAME\fame2010", replace


insheet using "H:\FAME\FAME Updated July 14\LN_FAME_1112.csv", clear
qui drop if identifier==.

qui keep identifier group_loans_asset group_loans_long_term group_loans_short_term ///
long_term_debt total_assets short_term_loans_overdrafts turnover

gen year=2011

qui bysort identifier: gen multi=_N
qui drop if multi>=2
qui drop multi

save "${hmrc_datadir}\FAME\fame2011", replace

use "${hmrc_datadir}\FAME\fame2011", clear
forval yyyy=2005/2010 {
append using "${hmrc_datadir}\FAME\fame`yyyy'"
}
save "${hmrc_datadir}\FAME\fame_leverage", replace

use "${hmrc_datadir}\ownership2\groupid_clean", clear
merge 1:m identifier using "${hmrc_datadir}\FAME\fame_leverage"
keep if _merge==3
drop _merge

sum group_loans_asset group_loans_long_term group_loans_short_term ///
long_term_debt short_term_loans_overdrafts total_assets turnover

foreach var of varlist group_loans_long_term group_loans_short_term ///
long_term_debt short_term_loans_overdrafts{
replace `var'=abs(`var')
}

drop if turnover<=0
bysort group year: egen group_turnover=total(turnover)
bysort group year: egen group_loans=total(group_loans_long_term)
bysort group year: egen group_asset=total(total_assets)
bysort group year: egen group_debt=total(long_term_debt)

gen leverage_ratio=group_loans/group_turnover
sum leverage_ratio, d
winsor leverage_ratio, gen (lvrg_ratio) p(0.01)
sum lvrg_ratio, d

gen leverage_ratio2=group_loans/group_asset
sum leverage_ratio2, d
winsor leverage_ratio2, gen (lvrg_ratio2) p(0.01)
sum lvrg_ratio2, d

gen intra_ratio=group_loans/group_debt
sum intra_ratio, d
winsor intra_ratio, gen (intra_debt_ratio) p(0.01)
sum intra_debt_ratio, d

histogram intra_ratio if intra_ratio>0, width(0.01) frac

keep identifier year lvrg_ratio lvrg_ratio2 intra_debt_ratio group
bysort group: egen lvrg_group=mean(lvrg_ratio)
bysort group: egen lvrg_group2=mean(lvrg_ratio2)
bysort group: egen intra_group=mean(intra_debt_ratio)

duplicates report year identifier
merge 1:m identifier year using "${hmrc_datadir}\export_reg_final_ukmncs_fy"
drop if _merge==1
drop _merge
label var lvrg_group "average group loan ratio of turnover "
label var lvrg_group2 "average group loan ratio of total assets "
label var intra_group "intra firm loan ratio to total debt"

save "${hmrc_datadir}\export_reg_final_ukmncs_fy_leverage", replace
